﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;

namespace Tilver.Data
{
    /// <summary>  
    /// 通用数据库访问类
    /// </summary>  
    public class DbDataBase
    {
        #region Fields

        /// <summary>
        /// 数据库连接串
        /// </summary>
        public string ConnectionString { get; set; }
        private DbProviderFactory _providerFactory;
        /// <summary>
        /// 数据库访问工厂
        /// </summary>
        public DbProviderFactory DbProviderFactory
        {
            get { return _providerFactory; }
            internal set { value = _providerFactory; }
        }

        private DbProviderType _dbProviderType;
        /// <summary>
        /// 数据库访问类型
        /// </summary>
        public DbProviderType DbProviderType
        {
            get { return _dbProviderType; }
            internal set { value = _dbProviderType; }
        }
        #endregion

        /// <summary>  
        /// 构造函数  
        /// </summary>  
        /// <param name="connectionString">数据库连接字符串</param>  
        /// <param name="providerType">数据库类型枚举</param>  
        public DbDataBase(string connectionString, DbProviderType providerType)
        {
            ConnectionString = connectionString;
            _dbProviderType = providerType;
            _providerFactory = DbDataFactory.GetDbProviderFactory(providerType);
            if (_providerFactory == null)
            {
                throw new ArgumentException("Can't load DbProviderFactory for given value of providerType!");
            }
        }
        /// <summary>
        /// 创建数据访问对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DbAdoBase CreateDbAdo(string sql, CommandType commandType)
        {
            return new DbAdoBase(sql, commandType, this);
        }
        /// <summary>
        /// 创建数据分页对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataPager.DbPager CreatePager(string sql, long pageSize, string keyField = null)
        {
            return new DataPager.DbPager(sql, pageSize, keyField, this);
        }

        #region ExecuteScalar
        /// <summary>
        /// 执行一个查询语句，返回查询结果的第一行第一列
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        internal object ExecuteScalar(CommandWrapper command)
        {
            return ExecuteCommand<object>(command, c => { return c.ExecuteScalar(); });
        }

        /// <summary>     
        /// 执行一个查询语句，返回查询结果的第一行第一列     
        /// </summary>     
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>     
        /// <returns></returns>     
        public Object ExecuteScalar(string commandText, IList<DbParameter> parameters, TransactionManager tm = null)
        {
            return ExecuteScalar(commandText, parameters, CommandType.Text, tm);
        }

        /// <summary>     
        /// 执行一个查询语句，返回查询结果的第一行第一列     
        /// </summary>     
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>     
        /// <param name="commandType">执行的SQL语句的类型</param>  
        /// <returns></returns>     
        public Object ExecuteScalar(string commandText, IList<DbParameter> parameters, CommandType commandType, TransactionManager tm = null)
        {
            /*
            using (DbCommand command = CreateCommand(commandText, parameters, commandType, tm))
            {
                if (command.Connection.State == ConnectionState.Closed)
                {
                    //command.Connection.Open();
                    OpenConnection(command.Connection);
                }
                object result = command.ExecuteScalar();
                if (command != null && command.Transaction == null && command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
                return result;
            }
            */
            var command = CreateCommand(commandText, commandType, parameters, tm);
            return ExecuteScalar(command);
        }

        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 对数据库执行增删改操作，返回受影响的行数
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        internal int ExecuteNonQuery(CommandWrapper command)
        {
            return ExecuteCommand<int>(command, c => { return c.ExecuteNonQuery(); });
        }

        /// <summary>     
        /// 对数据库执行增删改操作，返回受影响的行数   
        /// </summary>     
        /// <param name="commandText">要执行的增删改的SQL语句</param>     
        /// <param name="parameters">执行增删改语句所需要的参数</param>  
        /// <returns></returns>    
        public int ExecuteNonQuery(string commandText, IList<DbParameter> parameters, TransactionManager tm = null)
        {
            return ExecuteNonQuery(commandText, parameters, CommandType.Text, tm);
        }
        /// <summary>     
        /// 对数据库执行增删改操作，返回受影响的行数
        /// </summary>     
        /// <param name="commandText">要执行的增删改的SQL语句</param>     
        /// <param name="parameters">执行增删改语句所需要的参数</param>  
        /// <param name="commandType">执行的SQL语句的类型</param>  
        /// <returns></returns>  
        public int ExecuteNonQuery(string commandText, IList<DbParameter> parameters, CommandType commandType, TransactionManager tm = null)
        {
            /*
            using (DbCommand command = CreateCommand(commandText, parameters, commandType, tm))
            {
                if (command.Connection.State == ConnectionState.Closed)
                {
                    //command.Connection.Open();
                    OpenConnection(command.Connection);
                }
                int affectedRows = command.ExecuteNonQuery();
                if (command != null && command.Transaction == null && command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
                return affectedRows;
            }
            */
            var command = CreateCommand(commandText, commandType, parameters, tm);
            return ExecuteNonQuery(command);
        }

        #endregion

        #region ExecuteReader
        /// <summary>
        /// 执行一个查询语句，返回一个关联的DataReader实例
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        internal DbDataReader ExecuteReader(CommandWrapper command)
        {
            DbDataReader ret = null;
            if (command.Connection.State == ConnectionState.Closed)
            {
                //command.Connection.Open();
                OpenConnection(command.Connection);
            }
            try
            {
                if (command.Transaction == null)
                {
                    ret = command.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    ret = command.ExecuteReader();
                }
            }
            catch (Exception exp)
            {
                if (command.Transaction == null && command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
                throw exp;
            }
            return ret;
        }
        /// <summary>     
        /// 执行一个查询语句，返回一个关联的DataReader实例
        /// </summary>     
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>  
        /// <returns></returns>   
        public DbDataReader ExecuteReader(string commandText, IList<DbParameter> parameters, TransactionManager tm = null)
        {
            return ExecuteReader(commandText, parameters, CommandType.Text, tm);
        }

        /// <summary>     
        /// 执行一个查询语句，返回一个关联的DataReader实例     
        /// </summary>     
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>  
        /// <param name="commandType">执行的SQL语句的类型</param>  
        /// <returns></returns>   
        public DbDataReader ExecuteReader(string commandText, IList<DbParameter> parameters, CommandType commandType, TransactionManager tm = null)
        {
            var command = CreateCommand(commandText, commandType, parameters, tm);
            return ExecuteReader(command);
            /*
            DbCommand command = CreateCommand(commandText, parameters, commandType, tm);
            if (command.Connection.State == ConnectionState.Closed)
            {
                //command.Connection.Open();
                OpenConnection(command.Connection);
            }
            if (command.Transaction == null)//没有事务，关闭reader对象同时也关闭Connection
            {
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            else
            {
                return command.ExecuteReader();
            }
            */
        }
        #endregion

        #region ExecuteDataTable

        /// <summary>     
        /// 执行一个查询语句，返回一个包含查询结果的DataTable     
        /// </summary>     
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>  
        /// <returns></returns>  
        public DataTable ExecuteDataTable(string commandText, IList<DbParameter> parameters)
        {
            return ExecuteDataTable(commandText, parameters, CommandType.Text);
        }
        /// <summary>     
        /// 执行一个查询语句，返回一个包含查询结果的DataTable     
        /// </summary>     
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>  
        /// <param name="commandType">执行的SQL语句的类型</param>  
        /// <returns></returns>  
        public DataTable ExecuteDataTable(string commandText, IList<DbParameter> parameters, CommandType commandType)
        {
            using (DbCommand command = CreateCommand(commandText, parameters, commandType))
            {
                using (DbDataAdapter adapter = _providerFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = command;
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }
        #endregion

        #region QueryForList

        /// <summary>  
        /// 查询多个实体集合  
        /// </summary>  
        /// <typeparam name="T">返回的实体集合类型</typeparam>  
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>  
        /// <returns></returns>  
        public List<T> QueryForList<T>(string commandText, IList<DbParameter> parameters) where T : new()
        {
            return QueryForList<T>(commandText, parameters, CommandType.Text);
        }

        /// <summary>  
        ///  查询多个实体集合  
        /// </summary>  
        /// <typeparam name="T">返回的实体集合类型</typeparam>  
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>     
        /// <param name="commandType">执行的SQL语句的类型</param>  
        /// <returns></returns>  
        public List<T> QueryForList<T>(string commandText, IList<DbParameter> parameters, CommandType commandType) where T : new()
        {
            DataTable data = ExecuteDataTable(commandText, parameters, commandType);
            return EntityReader.GetEntities<T>(data);
        }
        /// <summary>  
        /// 查询单个实体  
        /// </summary>  
        /// <typeparam name="T">返回的实体集合类型</typeparam>  
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>  
        /// <returns></returns>  
        public T QueryForObject<T>(string commandText, IList<DbParameter> parameters) where T : new()
        {
            return QueryForObject<T>(commandText, parameters, CommandType.Text);
        }

        /// <summary>  
        /// 查询单个实体  
        /// </summary>  
        /// <typeparam name="T">返回的实体集合类型</typeparam>  
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>     
        /// <param name="commandType">执行的SQL语句的类型</param>  
        /// <returns></returns>  
        public T QueryForObject<T>(string commandText, IList<DbParameter> parameters, CommandType commandType) where T : new()
        {
            List<T> list = QueryForList<T>(commandText, parameters, commandType);
            if (list.Count > 0)
            {
                return list[0];
            }
            else
            {
                return default(T);
            }
        }
        #endregion

        #region CreateParameter

        /// <summary>
        /// 创建SQL参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="parameterDirection"></param>
        /// <returns></returns>
        public DbParameter CreateParameter(string name, object value, DbType dbType = DbType.String, int size = 0, ParameterDirection parameterDirection = ParameterDirection.Input)
        {
            DbParameter parameter = _providerFactory.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = parameterDirection;
            parameter.DbType = dbType;
            parameter.Size = size;
            return parameter;
        }
        #endregion

        #region DbCommand

        /// <summary>  
        /// 创建一个DbCommand对象  
        /// </summary>  
        /// <param name="commandText">要执行的查询语句</param>     
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>  
        /// <param name="commandType">执行的SQL语句的类型</param>  
        /// <returns></returns>  
        public DbCommand CreateCommand(string commandText, IList<DbParameter> parameters, CommandType commandType, TransactionManager tm = null)
        {
            //DbConnection connection = _providerFactory.CreateConnection();
            //connection.ConnectionString = ConnectionString;
            DbCommand command = _providerFactory.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            if (parameters != null && parameters.Count > 0)
            {
                foreach (DbParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            if (tm != null)//执行事务
            {
                DbTransaction tran = tm.GetTransaction(this);
                command.Transaction = tran;
                command.Connection = tran.Connection;
            }
            else
            {
                if (command.Connection == null)
                {
                    command.Connection = CreateConnection();
                }
                //DbConnection connection = _providerFactory.CreateConnection();
                //connection.ConnectionString = ConnectionString;
                //command.Connection = connection;
            }

            return command;
        }

        /// <summary>
        /// 创建DbCommand包装对象
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <param name="tm"></param>
        /// <returns></returns>
        internal CommandWrapper CreateCommand(string commandText, CommandType commandType = CommandType.Text, IList<DbParameter> parameters = null, TransactionManager tm = null)
        {
            DbCommand command = _providerFactory.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            if (parameters != null && parameters.Count > 0)
            {
                foreach (DbParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            if (tm != null)
            {
                DbTransaction tran = tm.GetTransaction(this);
                command.Transaction = tran;
                command.Connection = tran.Connection;
            }
            else
            {
                //DbConnection connection = _providerFactory.CreateConnection();
                //connection.ConnectionString = ConnectionString;
                //command.Connection = connection;
                if (command.Connection == null)
                {
                    command.Connection = CreateConnection();
                }
            }
            return new CommandWrapper(command, ConnectionString);
        }

        /// <summary>
        /// 创建DbCommand包装对象
        /// </summary>
        /// <param name="command"></param>
        /// <param name="tm"></param>
        /// <returns></returns>
        internal CommandWrapper CreateCommand(CommandWrapper command, TransactionManager tm = null)
        {
            if (tm != null)
            {
                DbTransaction tran = tm.GetTransaction(this);
                command.Connection = tran.Connection;
                command.Transaction = tran;
            }
            else
            {
                if (command.Connection == null)
                {
                    command.Connection = CreateConnection();
                }
            }
            return command;
        }
        /// <summary>
        /// 执行Command对象方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        protected T ExecuteCommand<T>(CommandWrapper command, Func<CommandWrapper, T> action)
        {
            T ret = default(T);
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                {
                    //command.Connection.Open();
                    OpenConnection(command.Connection);
                }
                try
                {
                    ret = action(command);
                }
                catch (Exception exp)
                {
                    throw exp;
                }
            }
            finally
            {
                if (command != null && command.Transaction == null && command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
            }
            return ret;
        }
        /// <summary>
        /// 创建一个DbConnection对象
        /// </summary>
        /// <returns></returns>
        public DbConnection CreateConnection()
        {
            var connection = _providerFactory.CreateConnection();
            connection.ConnectionString = ConnectionString;
            return connection;
        }
        /// <summary>
        /// 打开一个数据库连接
        /// </summary>
        /// <param name="conn"></param>
        public void OpenConnection(DbConnection conn)
        {
            if (string.IsNullOrEmpty(conn.ConnectionString))
                conn.ConnectionString = ConnectionString;
            conn.Open();
        }
        #endregion



    }
}
